﻿using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Threading.Tasks;
using System.Windows.Threading;
using static System.Windows.Application;

namespace Red.Launcher.Utilities
{
    /// <summary>
    /// Simplifies the process of interacting with a database
    /// </summary>
    public static class DatabaseHelper
    {
        public enum Result
        {
            /// <summary>
            /// There were no errors
            /// </summary>
            Success,

            /// <summary>
            /// Failed to connect to the database
            /// </summary>
            Connection,

            /// <summary>
            /// There was a problem with the SQL
            /// </summary>
            Sql,
        }

        /// <summary>
        /// Delegate type which is called for each item returned by a query
        /// </summary>
        /// <param name="row">All the values extracted from the sql reader</param>
        /// <seealso cref="Fetch"/>
        /// <seealso cref="FetchAsync"/>
        public delegate void FetchItemCallback( Dictionary<string, object> row );

        /// <summary>
        /// Delegate type which is called at the end of a fetch operation (results returned)
        /// </summary>
        /// <param name="resultsReturned">Total number of rows returned by the query. Will match the number of times <see cref="FetchItemCallback"/> was called</param>
        /// <param name="error">If there were no problems, this will be <see cref="Error.Success"/></param>
        /// <seealso cref="Fetch"/>
        /// <seealso cref="FetchAsync"/>
        public delegate void FetchCompleteCallback( int resultsReturned, Result error );

        /// <summary>
        /// Delegate type called at the end of an execute operation (no results returned)
        /// </summary>
        /// <param name="error">If there were no problems, this will be <see cref="Error.Success"/></param>
        /// <seealso cref="Execute"/>
        /// <seealso cref="ExecuteAsync"/>
        public delegate void ExecuteCompleteCallback( Result error );

        /// <summary>
        /// Perform a synchronous SQL query
        /// </summary>
        /// <param name="sql">SQL to execute</param>
        /// <param name="databaseConnectionString">Database upon which to perform the query</param>
        /// <param name="itemCallback">Called for every item returned by the query</param>
        /// <param name="completeCallback">Called after every item has been processed, may be <see langword="null"/></param>
        /// <param name="expectOne">Set this to <see langword="null"/> if your SQL query should only return one result (internally causes the function to process the first result and ignore the rest)</param>
        /// <seealso cref="FetchAsync"/>
        /// <seealso cref="FetchAsyncSafe"/>
        /// <seealso cref="Execute"/>
        /// <seealso cref="ExecuteAsync"/>
        /// <seealso cref="ExecuteAsyncSafe"/>
        public static void Fetch( string sql, string databaseConnectionString, FetchItemCallback itemCallback, FetchCompleteCallback completeCallback = null, bool expectOne = false )
        {
            using ( var sqlConnection = new SqlConnection( databaseConnectionString ) )
            {
                SqlCommand command = new SqlCommand
                {
                    CommandText = sql,
                    Connection = sqlConnection
                };

                Result error = Result.Success;
                int count = 0;

                try
                {
                    sqlConnection.Open();
                    SqlDataReader reader = command.ExecuteReader();

                    while ( reader.Read() )
                    {
                        if ( !expectOne || count == 0 )
                        {
                            Dictionary<string, object> row = new Dictionary<string, object>();

                            for ( int i = 0; i < reader.FieldCount; ++i )
                            {
                                string column = reader.GetName( i );
                                row[ column ] = reader[ column ];
                            }

                            Current.Dispatcher.BeginInvoke( DispatcherPriority.Normal, itemCallback, row );
                        }

                        ++count;
                    }
                }
                catch ( SqlException ex )
                {
                    error = ( ex.ErrorCode == -2146232060 ) ? Result.Connection : Result.Sql;
                    Debug.Print( "Failed to execute SQL \"{0}\".\nConnection String: {1}\n{2}", sql, databaseConnectionString, ex );
                }

                // https://msdn.microsoft.com/en-us/library/ms591587.aspx
                // If multiple BeginInvoke calls are made at the same DispatcherPriority, they will be executed in the order the calls were made.
                if ( completeCallback != null )
                {
                    Current.Dispatcher.BeginInvoke( completeCallback, DispatcherPriority.Normal, new object[] { count, error } );
                }
            }
        }

        /// <summary>
        /// Perform an asynchronous database query which could return any number of results.
        /// 
        /// Typically you would use this for performing Select SQL operations.
        /// 
        /// The <see cref="FetchItemCallback"/> and <see cref="FetchCompleteCallback"/> may be invoked after execution has returned to the calling function! (Order of execution is not guaranteed!)
        /// </summary>
        /// <param name="sql">SQL to execute</param>
        /// <param name="databaseConnectionString">Database upon which to perform the query</param>
        /// <param name="itemCallback">Called for every item returned by the query</param>
        /// <param name="completeCallback">Called after every item has been processed, may be <see langword="null"/></param>
        /// <param name="expectOne">Set this to <see langword="true"/> if your SQL query should only return one result (internally causes the function to process the first result and ignore the rest)</param>
        /// <remarks>Asynchronous wrapper for <see cref="Fetch"/></remarks>
        /// <seealso cref="Fetch"/>
        /// <seealso cref="FetchAsyncSafe"/>
        /// <seealso cref="Execute"/>
        /// <seealso cref="ExecuteAsync"/>
        /// <seealso cref="ExecuteAsyncSafe"/>
        public static Task FetchAsync( string sql, string databaseConnectionString, FetchItemCallback itemCallback, FetchCompleteCallback completeCallback = null, bool expectOne = false )
        {
            return Task.Run
            (
                () =>
                {
                    Fetch( sql, databaseConnectionString, itemCallback, completeCallback, expectOne );
                }
            );
        }

        /// <summary>
        /// Perform an asynchronous database query which could return any number of results.
        /// 
        /// Typically you would use this for performing Select SQL operations.
        /// 
        /// This "Safe" function enforces the order of execution. The <see cref="FetchCompleteCallback"/> will be invoked before returning execution to the calling function
        /// </summary>
        /// <returns>true on success, false if there was an error</returns>
        /// <param name="sql">SQL to execute</param>
        /// <param name="databaseConnectionString">Database upon which to perform the query</param>
        /// <param name="itemCallback">Called for every item returned by the query</param>
        /// <param name="completeCallback">Called after every item has been processed, may be <see langword="null"/></param>
        /// <param name="expectOne">Set this to <see langword="true"/> if your SQL query should only return one result (internally causes the function to process the first result and ignore the rest)</param>
        /// <remarks>Asynchronous wrapper for <see cref="Fetch"/></remarks>
        /// <seealso cref="Fetch"/>
        /// <seealso cref="FetchAsync"/>
        /// <seealso cref="Execute"/>
        /// <seealso cref="ExecuteAsync"/>
        /// <seealso cref="ExecuteAsyncSafe"/>
        public static async Task<Result> FetchAsyncSafe( string sql, string databaseConnectionString, FetchItemCallback itemCallback, FetchCompleteCallback completeCallback = null, bool expectOne = false )
        {
            TaskCompletionSource<Result> tcs = new TaskCompletionSource<Result>();
            
            FetchCompleteCallback completeCallbackWrapper = ( int resultsReturned, Result error ) => { completeCallback?.Invoke( resultsReturned, error ); tcs.SetResult( error ); };
            await FetchAsync( sql, databaseConnectionString, itemCallback, completeCallbackWrapper, expectOne );

            return await tcs.Task;
        }

        /// <summary>
        /// Perform a synchronous database query for which no results are expected.
        /// 
        /// Typically you would use this for performing Insert/Update/Delete SQL operations.
        /// 
        /// The <see cref="ExecuteCompleteCallback"/> may be invoked after execution has returned to the calling function! (Order of execution is not guaranteed!)
        /// </summary>
        /// <param name="sql">SQL to execute</param>
        /// <param name="databaseConnectionString">Database upon which to perform the query</param>
        /// <param name="completeCallback">Called after every item has been processed, may be <see langword="null"/></param>
        /// <seealso cref="Fetch"/>
        /// <seealso cref="FetchAsync"/>
        /// <seealso cref="FetchAsyncSafe"/>
        /// <seealso cref="ExecuteAsync"/>
        /// <seealso cref="FetchAsyncSafe"/>
        public static void Execute( string sql, string databaseConnectionString, ExecuteCompleteCallback completeCallback = null )
        {
            Execute( sql, databaseConnectionString, new SqlParameter[ 0 ], completeCallback );
        }

        /// <summary>
        /// Perform a synchronous database query for which no results are expected.
        /// 
        /// Typically you would use this for performing Insert/Update/Delete SQL operations.
        /// 
        /// The <see cref="ExecuteCompleteCallback"/> may be invoked after execution has returned to the calling function! (Order of execution is not guaranteed!)
        /// </summary>
        /// <param name="sql">SQL to execute</param>
        /// <param name="databaseConnectionString">Database upon which to perform the query</param>
        /// <param name="parameters">An array of <cref="SqlParameter"/> which encapsulate values that will be passed to the database</param>
        /// <param name="completeCallback">Called after every item has been processed, may be <see langword="null"/></param>
        /// <seealso cref="Fetch"/>
        /// <seealso cref="FetchAsync"/>
        /// <seealso cref="FetchAsyncSafe"/>
        /// <seealso cref="ExecuteAsync"/>
        /// <seealso cref="FetchAsyncSafe"/>
        public static void Execute( string sql, string databaseConnectionString, SqlParameter[] parameters, ExecuteCompleteCallback completeCallback = null )
        {
            using ( var sqlConnection = new SqlConnection( databaseConnectionString ) )
            {
                SqlCommand command = new SqlCommand
                {
                    CommandText = sql,
                    Connection = sqlConnection
                };

                command.Parameters.AddRange( parameters );

                Result error = Result.Success;

                try
                {
                    sqlConnection.Open();
                    command.ExecuteNonQuery();
                }
                catch ( SqlException ex )
                {
                    // 50000 signals the start of the user defined error codes
                    error = ( ex.ErrorCode == -2146232060 && ex.Number != 50000 ) ? Result.Connection : Result.Sql;
                    Debug.Print( "Failed to execute SQL \"{0}\".\nConnection String: {1}\n{2}", sql, databaseConnectionString, ex );
                }

                if ( completeCallback != null )
                {
                    Current.Dispatcher.BeginInvoke( completeCallback, DispatcherPriority.Normal, new object[] { error } );
                }
            }
        }

        /// <summary>
        /// Perform an asynchronous database query for which no results are expected.
        /// 
        /// Typically you would use this for performing Insert/Update/Delete SQL operations.
        /// </summary>
        /// <param name="sql">SQL to execute</param>
        /// <param name="databaseConnectionString">Database upon which to perform the query</param>
        /// <param name="completeCallback">Called after every item has been processed, may be <see langword="null"/></param>
        /// <remarks>Asynchronous wrapper for <see cref="Execute"/></remarks>
        /// <seealso cref="Fetch"/>
        /// <seealso cref="FetchAsync"/>
        /// <seealso cref="FetchAsyncSafe"/>
        /// <seealso cref="Execute"/>
        public static Task ExecuteAsync( string sql, string databaseConnectionString, ExecuteCompleteCallback completeCallback = null )
        {
            return Task.Run
            (
                () =>
                {
                    Execute( sql, databaseConnectionString, completeCallback );
                }
            );
        }

        /// <summary>
        /// Perform an asynchronous database query for which no results are expected.
        /// 
        /// Typically you would use this for performing Insert/Update/Delete SQL operations.
        /// </summary>
        /// <param name="databaseConnectionString">Database upon which to perform the query</param>
        /// <param name="sql">SQL to execute</param>
        /// <param name="parameters">An array of <cref="SqlParameter"/> which encapsulate values that will be passed to the database</param>
        /// <param name="completeCallback">Called after every item has been processed, may be <see langword="null"/></param>
        /// <remarks>Asynchronous wrapper for <see cref="Execute"/></remarks>
        /// <seealso cref="Fetch"/>
        /// <seealso cref="FetchAsync"/>
        /// <seealso cref="FetchAsyncSafe"/>
        /// <seealso cref="Execute"/>
        public static Task ExecuteAsync( string sql, string databaseConnectionString, SqlParameter[] parameters, ExecuteCompleteCallback completeCallback = null )
        {
            return Task.Run
            (
                () =>
                {
                    Execute( sql, databaseConnectionString, parameters, completeCallback );
                }
            );
        }

        /// <summary>
        /// Perform an asynchronous database query for which no results are expected.
        /// 
        /// Typically you would use this for performing Insert/Update/Delete SQL operations.
        /// 
        /// This "Safe" function enforces the order of execution. The <see cref="ExecuteCompleteCallback"/> will be invoked before returning execution to the calling function
        /// </summary>
        /// <param name="sql">SQL to execute</param>
        /// <param name="databaseConnectionString">Database upon which to perform the query</param>
        /// <param name="completeCallback">Called after every item has been processed, may be <see langword="null"/></param>
        /// <remarks>Asynchronous wrapper for <see cref="Execute"/></remarks>
        /// <seealso cref="Fetch"/>
        /// <seealso cref="FetchAsync"/>
        /// <seealso cref="Execute"/>
        /// <seealso cref="ExecuteAsync"/>
        public static async Task<Result> ExecuteAsyncSafe( string sql, string databaseConnectionString, ExecuteCompleteCallback completeCallback = null )
        {
            TaskCompletionSource<Result> tcs = new TaskCompletionSource<Result>();

            ExecuteCompleteCallback completeCallbackWrapper = ( Result error ) => { completeCallback?.Invoke( error ); tcs.SetResult( error ); };
            await ExecuteAsync( sql, databaseConnectionString, completeCallbackWrapper );

            return await tcs.Task;
        }

        /// <summary>
        /// Perform an asynchronous database query for which no results are expected.
        /// 
        /// Typically you would use this for performing Insert/Update/Delete SQL operations.
        /// 
        /// This "Safe" function enforces the order of execution. The <see cref="ExecuteCompleteCallback"/> will be invoked before returning execution to the calling function
        /// </summary>
        /// <param name="sql">SQL to execute</param>
        /// <param name="databaseConnectionString">Database upon which to perform the query</param>
        /// <param name="parameters">An array of <cref="SqlParameter"/> which encapsulate values that will be passed to the database</param>
        /// <param name="completeCallback">Called after every item has been processed, may be <see langword="null"/></param>
        /// <remarks>Asynchronous wrapper for <see cref="Execute"/></remarks>
        /// <seealso cref="Fetch"/>
        /// <seealso cref="FetchAsync"/>
        /// <seealso cref="Execute"/>
        /// <seealso cref="ExecuteAsync"/>
        public static async Task<Result> ExecuteAsyncSafe( string sql, string databaseConnectionString, SqlParameter[] parameters, ExecuteCompleteCallback completeCallback = null )
        {
            TaskCompletionSource<Result> tcs = new TaskCompletionSource<Result>();

            ExecuteCompleteCallback completeCallbackWrapper = ( Result error ) => { completeCallback?.Invoke( error ); tcs.SetResult( error ); };
            await ExecuteAsync( sql, databaseConnectionString, parameters, completeCallbackWrapper );

            return await tcs.Task;
        }
    }
}
